package com.francis.springbootcommonutil.excel;

import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileInputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

import static java.math.BigDecimal.ROUND_HALF_UP;

/**
 * <p>Title: excel</p>
 * <p>Description: excel</p>
 * <p>Copyright: ChenWei Copyright(c) 2018/p>
 *
 * @author ChenWei
 * @version 0.0.0.1
 * <pre>Histroy:
 *       2020/6/11 17:48 Create by ChenWei
 * </pre>
 */
@Slf4j
public class AnalyseExcel {


    public static void main(String[] args) {
        String filePath = "D:/StackingGoods.xlsx";
        List<StackingGoods> goodsList = getExcelData(null, filePath);
        System.out.println(goodsList.size());

        System.out.println(JSONObject.toJSON(goodsList.get(0)).toString());

    }

    /**
     * @param file MultipartFile 文件   filePath  excel 文件路径
     * @return java.util.List<com.francis.springbootcommonutil.excel.StackingGoods>
     * @methodName getExcelData
     * @description 解析excel 数据   注意：    HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls
     * XSSFWorkbook:是操作Excel2007的版本，扩展名是.xlsx
     * @author ChenWei
     * @date 2020/6/11 18:30
     */
    public static List<StackingGoods> getExcelData(MultipartFile file, String filePath) {
        List<StackingGoods> goodsList = new ArrayList<>();
        InputStream is;
        try {
            is = file == null ? new FileInputStream(filePath) : file.getInputStream();
//            HSSFWorkbook hfw = new HSSFWorkbook(is);
            XSSFWorkbook hfw = new XSSFWorkbook(is);
            //获取工作簿
            for (int numSheet = 0; numSheet < hfw.getNumberOfSheets(); numSheet++) {
//                HSSFSheet hssfSheet = hfw.getSheetAt(numSheet);
                XSSFSheet hssfSheet = hfw.getSheetAt(numSheet);
                if (hssfSheet == null) {
                    continue;
                }
                //获取当前工作簿的每一行
                for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
//                    HSSFRow row = hssfSheet.getRow(rowNum);
                    XSSFRow row = hssfSheet.getRow(rowNum);
                    if (row != null) {
                        StackingGoods goods = new StackingGoods();
                        // 将excel 单元格的值转换成Javabean
                        goods = buildGoods(goods, row);
                        goodsList.add(goods);
                    }
                }
            }
        } catch (java.io.IOException e) {
            log.error("excel.getXlsData() 异常了：" + e.getMessage());
            e.printStackTrace();
        }
        return goodsList;
    }


    /**
     * @param goods, row
     * @return com.francis.springbootcommonutil.excel.StackingGoods
     * @methodName buildGoods
     * @description 将excel中值转换成Javabean
     * @author ChenWei
     * @date 2020/6/22 14:17
     */
    private static StackingGoods buildGoods(StackingGoods goods, XSSFRow row) {

        // excel单元格中值可能是数字类型，这里要将code转成string
        if (row.getCell(0).getCellTypeEnum().equals(CellType.NUMERIC)) {
            goods.setCode(String.valueOf(row.getCell(0).getRawValue()));
        } else {
            goods.setCode(String.valueOf(row.getCell(0)));
        }
        goods.setName(String.valueOf(row.getCell(1)));
        goods.setModel(String.valueOf(row.getCell(2)));

        // 获取小数值
        XSSFCell cell3 = row.getCell(3);
        goods.setLength(String.valueOf(cell3).isEmpty() ? null : new BigDecimal(cell3.getRawValue()));
        XSSFCell cell4 = row.getCell(4);
        goods.setWide(String.valueOf(cell4).isEmpty() ? null : new BigDecimal(cell4.getRawValue()));
        XSSFCell cell5 = row.getCell(5);
        goods.setHigh(String.valueOf(cell5).isEmpty() ? null : new BigDecimal(cell5.getNumericCellValue()));

        if (row.getCell(7).getCellTypeEnum().equals(CellType.NUMERIC)) {
            goods.setBarCode(String.valueOf(row.getCell(7).getRawValue()));
        } else {
            goods.setBarCode(String.valueOf(row.getCell(7)));
        }
        if (row.getCell(8).getCellTypeEnum().equals(CellType.NUMERIC)) {
            goods.setNote(String.valueOf(row.getCell(8).getRawValue()));
        } else {
            goods.setNote(String.valueOf(row.getCell(8)));
        }
        goods.setPosition(String.valueOf(row.getCell(10)));
        XSSFCell cell9 = row.getCell(9);
        // 四舍五入 保留1为小数
        goods.setWeight(String.valueOf(cell9).isEmpty() ? null : new BigDecimal(cell9.getNumericCellValue()).setScale(1, ROUND_HALF_UP));
        goods.setNoteA(String.valueOf(row.getCell(6)));
        return goods;
    }


}

